﻿using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace IBuy.DataAccessLayer
{
    /// <summary>
    /// 类，用于数据访问的类。
    /// </summary>
    public class Database : IDisposable,IDatabase
    {
        /// <summary>
        /// 保护变量，数据库连接。
        /// </summary>
        protected OleDbConnection Connection;

        /// <summary>
        /// 保护变量，数据库连接串。
        /// </summary>
        protected String ConnectionString;

        /// <summary>
        /// 构造函数。
        /// </summary>
        /// <param name="DatabaseConnectionString">数据库连接串</param>
        public Database()
        {
            try
            {
                ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            }
            catch (Exception e)
            {
                ConnectionString = "provider=microsoft.Jet.OleDb.4.0;data source=d:\\\\DataBase\\IBuy.mdb;";
            }
        }

        /// <summary>
        /// 构造函数。
        /// </summary>
        /// <param name="pDatabaseConnectionString">数据库连接串</param>
        public Database(string pDatabaseConnectionString)
        {
            ConnectionString = pDatabaseConnectionString;
        }

        /// <summary>
        /// 析构函数，释放非托管资源
        /// </summary>
        ~Database()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch { }
            try
            {
                Dispose();
            }
            catch { }
        }

        /// <summary>
        /// 保护方法，打开数据库连接。
        /// </summary>
        protected void Open()
        {
            if (Connection == null)
            {
                Connection = new OleDbConnection(ConnectionString);
            }
            if (Connection.State.Equals(ConnectionState.Closed))
            {
                try
                {
                    String path2 = System.AppDomain.CurrentDomain.BaseDirectory;

                    //获取和设置包括该应用程序的目录的名称。
                    String path3 = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;

                    //获取启动了应用程序的可执行文件的路径，不包括可执行文件的名称。
                    String path4 = System.Windows.Forms.Application.StartupPath;


                    //获取启动了应用程序的可执行文件的路径及文件名
                    String path5 = System.Windows.Forms.Application.ExecutablePath;

                    Connection.Open();
                }
                catch (Exception e)
                {
                }
            }
        }

        /// <summary>
        /// 公有方法，关闭数据库连接。
        /// </summary>
        public void Close()
        {
            if (Connection != null)
                Connection.Close();
        }

        /// <summary>
        /// 公有方法，释放资源。
        /// </summary>
        public void Dispose()
        {
            // 确保连接被关闭
            if (Connection != null)
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        /// <summary>
        /// 公有方法，获取数据，返回一个SqlDataReader （调用后主意调用SqlDataReader.Close()）。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <returns>SqlDataReader</returns>
        public OleDbDataReader GetDataReader(String SqlString)
        {
            Open();
            OleDbCommand cmd = new OleDbCommand(SqlString, Connection);
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 公有方法，获取数据，返回一个DataSet。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(String SqlString)
        {
            Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(SqlString, Connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Close();
            return dataset;
        }
        /// <summary>
        /// 公有方法，获取一个SqlDataAdapter
        /// </summary>
        /// <param name="SqlString">一个select的SQL语句</param>
        /// <returns>SqlDataAdapter</returns>
        public OleDbDataAdapter GetDataAdapter(String SqlString)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand();
            Open();
            command.Connection = Connection;
            command.CommandText = SqlString;
            adapter.SelectCommand = command;
            return adapter;
        }
        /// <summary>
        /// 公有方法，获取数据，返回一个DataRow。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataRow</returns>
        public DataRow GetDataRow(String SqlString)
        {
            DataSet dataset = GetDataSet(SqlString);
            dataset.CaseSensitive = false;
            if (dataset.Tables[0].Rows.Count > 0)
            {
                return dataset.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 公有方法，执行Sql语句。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <returns>对Update、Insert、Delete为影响到的行数，其他情况为-1</returns>
        public int ExecuteSQL(String SqlString)
        {
            int count = -1;
            Open();
            try
            {
                OleDbCommand cmd = new OleDbCommand(SqlString, Connection);
                count = cmd.ExecuteNonQuery();
            }
            catch
            {
                count = -1;
            }
            finally
            {
                Close();
            }
            return count;
        }

        /// <summary>
        /// 公有方法，执行一组Sql语句。
        /// </summary>
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>bool类型，是否成功</returns>
        public bool ExecuteSQL(ArrayList SqlStrings)
        {
            bool success = true;
            Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }
            return success;
        }

        /// <summary>
        /// 公有方法，在一个数据表中插入一条记录。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
        /// <returns>bool类型，是否成功</returns>
        public bool Insert(String TableName, Hashtable Cols)
        {
            int Count = 0;

            if (Cols.Count <= 0)
            {
                return true;
            }

            String Fields = " (";
            String Values = " Values('";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                    Values += "','";
                }
                Fields += item.Key.ToString();
                Values += item.Value.ToString();
                Count++;
            }
            Fields += ")";
            Values += "')";

            String SqlString = "Insert into " + TableName + Fields + Values;

            if (ExecuteSQL(SqlString) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 公有方法，更新一个数据表。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
        /// <param name="Where">string类型，Where子句</param>
        /// <returns>bool类型，操作是否成功</returns>
        public bool Update(String TableName, Hashtable Cols, String Where)
        {
            int Count = 0;
            if (Cols.Count <= 0)
            {
                return true;
            }
            String Fields = " ";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += "',";
                }
                Fields += item.Key.ToString();
                Fields += "='";
                Fields += item.Value.ToString();
                Count++;
            }
            Fields += "' ";

            String SqlString = "Update " + TableName + " Set " + Fields + Where;

            if (ExecuteSQL(SqlString) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 公有方法，从数据库中删除记录
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="where">string类型，条件语句</param>
        /// <returns>布尔值bool，操作是否成功</returns>
        public bool Delete(string TableName, string where)
        {
            String SqlString = "delete from "+TableName+where;
            if (ExecuteSQL(SqlString) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}